package com.icefish.daoImpl;

import com.icefish.bean.User;
import com.icefish.dao.UserDao;
import com.icefish.utils.DatabaseConnFactory;

import java.sql.*;

/**
 * Description:
 * Date: 2022/12/1 20:59
 * Author: 26480
 *
 * @since JDK 1.8
 */
public class UserDaoImpl implements UserDao {
    // 通过用户名，密码 查询 用户数据
    @Override
    public Integer selectByNamePWD (String username,String password) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        Connection conn = DatabaseConnFactory.getConn();

        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,username);
        pstmt.setString(2,password);
        ResultSet rs = pstmt.executeQuery();

        Integer user_id = 0;

        while (rs.next()) {
            user_id = rs.getInt("user_id");
        }

        // 关闭连接，释放资源
        if (rs != null) {
        }
        rs.close();
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }

        return user_id;

    }

    // 查询用户的所有数据
    @Override
    public User selectByID(Integer user_id) throws SQLException{
        String sql = "SELECT * FROM users WHERE user_id = ?";
        Connection conn = DatabaseConnFactory.getConn();

        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,user_id);
        ResultSet rs = pstmt.executeQuery();

        User userRt = new User();

        while (rs.next()) {
            userRt.setUser_id(rs.getInt("user_id"));
            userRt.setUsername(rs.getString("username"));
            userRt.setPassword(rs.getString("password"));
            userRt.setLevel(rs.getInt("level"));
            userRt.setGrade(rs.getString("grade"));
            userRt.setType(rs.getString("type"));
            userRt.setWallet(rs.getInt("wallet"));
            userRt.setUser_picture(rs.getString("user_picture"));
            userRt.setState_message(rs.getString("state_message"));
        }

        // 关闭连接，释放资源
        if (rs != null) {
            rs.close();
        }
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }

        return userRt;
    }

    // 添加用户
    @Override
    public Boolean inserUser(User user) throws SQLException {

        String sql = "INSERT  INTO  users (username, password,user_picture,type,level,grade,wallet,state_message) VALUES (?,?,?,?,?,?,?,?)";
        Connection conn = DatabaseConnFactory.getConn();

        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,user.getUsername());
        pstmt.setString(2,user.getPassword());
        pstmt.setString(3,user.getUser_picture());
        pstmt.setString(4,user.getType());

        // 默认数据
        pstmt.setInt(5,user.getLevel());
        pstmt.setString(6,user.getGrade());
        pstmt.setInt(7,user.getWallet());
        pstmt.setString(8,user.getState_message());

        //执行DML语句，返回受影响的记录条数
        Integer isok = pstmt.executeUpdate();

        // 关闭连接，释放资源
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }

        return  (isok == 1);
    }

    @Override
    public Integer selectWalletByUserId(Integer user_id) throws SQLException {

        String sql="select wallet from users where user_id=?";
        Connection conn = DatabaseConnFactory.getConn();

        PreparedStatement psmt = conn.prepareStatement(sql);
        psmt.setInt(1,user_id);

        ResultSet rst = psmt.executeQuery();

        rst.next();

        Integer userId = rst.getInt("wallet");

        conn.close();
        psmt.close();
        rst.close();

        return userId;

    }

    @Override
    public boolean updateUserWallet(Integer userId, Integer wallet)throws SQLException {

        String sql = "update users set wallet = ? where user_id=?";
        Connection conn = DatabaseConnFactory.getConn();

        PreparedStatement psmt = conn.prepareStatement(sql);
        psmt.setInt(1,wallet);
        psmt.setInt(2,userId);

        int i = psmt.executeUpdate();

        conn.close();
        psmt.close();

        return (i == 1);
    }
}
